﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Text.RegularExpressions;
using AcctrueWMS.Foundation.Common;
using AcctrueWMS.Foundation.Common.Exception;

namespace AcctrueWMS.Foundation.DAL
{
    //词法分析中使用了大量的正则，以减轻工作量，正则的维护较为麻烦
    public class WMSqlInterpreter
    {
        private static readonly String dbType = ConfigurationManager.AppSettings["DbType"].ToLower();

        //匹配成对的括号，要理解这个表达式，需要理解断言和分组构造
        private static Regex subquery_reg = new Regex(@"\((?<subquery>(?>[^()]+|\((?<DEPTH>)|\)(?<-DEPTH>))*(?(DEPTH)(?!)))\)");

        //分别对select子句，from子句，where子句，group子句，order子句和range子句做分析
        private static Regex select_reg = new Regex(@"(?in)(?<select>select\s+(?<list>.*?)"
            + @"\s+from\s+(?<from>((?!(where|order\s+by|group\s+by|range)).)+))"
            + @"(where\s+(?<where>((?!(order\s+by|group\s+by|range)\s+).)+))?"
            + @"(group\s+by\s+(?<group>((?!(order\s+by\s+|range\s+)).)+))?"
            + @"(order\s+by\s+(?<order>((?!(range\s+)).)+))?"
            + @"(range\s+(?<range>.+))?");

        private static Regex insert_reg = new Regex(@"insert\s+", RegexOptions.Compiled);    //匹配insert
        private static Regex delete_reg = new Regex(@"delete\s+", RegexOptions.Compiled);  // 匹配update
        private static Regex update_reg = new Regex(@"update\s+", RegexOptions.Compiled);  // 匹配delete

        private static Regex hasrange_reg=new Regex(@"\s+range\s+");
        private static Regex range_reg = new Regex(@"(?<startIndex>[\d]+),(?<endIndex>[\d]+)(,(?<datakey>\S+))?",RegexOptions.Compiled);

        //判断语句类型
        private SQLCommandTypeEnum GetCommandType(string cmdText)
        {
            if (insert_reg.IsMatch(cmdText))
                return SQLCommandTypeEnum.Insert;
            if (update_reg.IsMatch(cmdText))
                return SQLCommandTypeEnum.Update;
            if (delete_reg.IsMatch(cmdText))
                return SQLCommandTypeEnum.Delete;
            if(select_reg.IsMatch(cmdText))
                return SQLCommandTypeEnum.Select;
            return SQLCommandTypeEnum.Other;
        }

        public string GetRealSql(string cmdText)
        {
            return ParseSql(cmdText);
        }

        private string ParseSelect(string cmdText)
        {
            string list = null;
            string from = null;
            string where = null;
            string group = null;
            string order = null;
            string range = null;

            if (!select_reg.IsMatch(cmdText))
                throw new WMSSysException("WMSql语法错误！");
            Match m = select_reg.Match(cmdText);

            list = m.Groups["list"].Value;
            from = m.Groups["from"].Value;
            where = m.Groups["where"].Value;
            group = m.Groups["group"].Value;
            order = m.Groups["order"].Value;
            range = m.Groups["range"].Value;
            return BuildSql(list, from, where, group, order, range);
        }
        private string ParseInsert(string cmdText)
        {
            return cmdText;
        }
        private string ParseUpdate(string cmdText)
        {
            return cmdText;
        }

        private string ParseDelete(string cmdText)
        {
            return cmdText;
        }
        private string ParseOther(string cmdText)
        {
            return cmdText;
        }

        static Regex paramReg = new Regex(@"@(?<param>[\w|\d]+)\s+",RegexOptions.Compiled);
        /// <summary>
        /// 将语句中的参数替换为本地数据库参数
        /// </summary>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        private string ParseParameters(string cmdText)
        {
            return paramReg.Replace(cmdText, new MatchEvaluator(ParamMatchEvaluate));
        }

        /// <summary>
        /// 替换数据库参数
        /// </summary>
        /// <param name="m"></param>
        /// <returns></returns>
        private string ParamMatchEvaluate(Match m)
        {
            switch(dbType)
            {
                case DBTypeConst.MSSQL:
                    return m.Value;
                case DBTypeConst.ORACLE:
                    return ":" + m.Groups["param"].Value;
                default:
                    return m.Value;
            }
            
        }
        static Regex dateReg = new Regex(@"date\((?<field>[\w|\d]+)\)");
        /// <summary>
        /// 处理时间格式的字段
        /// </summary>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        //private string ParseDateField(string cmdText)
        //{
        //    dateReg.Replace(cmdText,);
        //}

        //private string DateFieldEvaluate(Match m)
        //{
        //    switch (dbType)
        //    {
        //        case DBTypeConst.ORACLE:
        //            return string.Format("to_date()");
        //    }
        //}

        //分析sql语句
        private string ParseSql(string cmdText)
        {
            //替换为本地参数
            cmdText = ParseParameters(cmdText);
            //优先分析子查询
            if (subquery_reg.IsMatch(cmdText))
            {
                Collection<string> subQueries=new Collection<string>();
                string standCommand = "";
                int curIndex=0;
                foreach (Match m in subquery_reg.Matches(cmdText))
                {
                    standCommand+=cmdText.Substring(curIndex,m.Index-curIndex);
                    curIndex = (m.Index + m.Length);
                    //分析子查询，并解释
                    subQueries.Add(ParseSql(m.Groups["subquery"].Value));
                    //采用占位法，在主语句中占位
                    standCommand+=(string.Format(" __sub__{0}",subQueries.Count-1));
                }
                standCommand += cmdText.Substring(curIndex, cmdText.Length - curIndex);

                //对主语句进行解释
                string tempCommand=ParseSql(standCommand);
                //将子查询回填
                for (int i = 0; i < subQueries.Count; i++)
                {
                    tempCommand=tempCommand.Replace("__sub__" + i, "("+subQueries[i]+")");
                }
                return tempCommand;
            }
            switch (GetCommandType(cmdText))
            {
                case SQLCommandTypeEnum.Select:
                    return ParseSelect(cmdText);
                case SQLCommandTypeEnum.Insert:
                    return ParseInsert(cmdText);
                case SQLCommandTypeEnum.Delete:
                    return ParseDelete(cmdText);
                case SQLCommandTypeEnum.Update:
                    return ParseUpdate(cmdText);
                default:
                    return ParseOther(cmdText);
            }
        }

        
        /// <summary>
        /// 构造sql语句
        /// </summary>
        /// <param name="list"></param>
        /// <param name="source"></param>
        /// <param name="where"></param>
        /// <param name="group"></param>
        /// <param name="order"></param>
        /// <param name="range"></param>
        /// <returns></returns>
        private string BuildSql(string list, string source, string where, string group, string order, string range)
        {
            switch (dbType)
            {
                case DBTypeConst.ORACLE:
                    return BuildOracleSql(list,source,where,group,order,range);
                case DBTypeConst.MSSQL:
                    return BuildMSSql(list,source,where,group,order,range);
                default:
                    return BuildMSSql(list,source,where,group,order,range);
            }
        }
        private string BuildOracleSql(string list, string source, string where, string group, string order, string range)
        {
            StringBuilder sb=new StringBuilder();
            //分析range语句
            if (!string.IsNullOrEmpty(range))
            {
                if(!range_reg.IsMatch(range))
                    throw new WMSException("range语句不符合格式！");
                Match rangeMatch=range_reg.Match(range);

                string startIndexStr = rangeMatch.Groups["startIndex"].Value;
                string endStartIndexStr = rangeMatch.Groups["endIndex"].Value;
                string dataKey = rangeMatch.Groups["datakey"].Value;

                //如果存在键值，则加入排序条件
                if (!string.IsNullOrEmpty(dataKey))
                {
                    if (!string.IsNullOrEmpty(order))
                        order += ",";
                    order += dataKey;
                }
                if (!string.IsNullOrEmpty(where))
                    where += " and";
                where += string.Format(" (ROWNUM>={0} and ROWNUM<={1})", startIndexStr, endStartIndexStr);
            }

            sb.Append(string.Format("select {0} from {1}", list, source));
            if (!string.IsNullOrEmpty(where))
                sb.Append(string.Format(" where {0}",where));
            if (!string.IsNullOrEmpty(group))
                sb.Append(string.Format(" group by {0}", group));
            if (!string.IsNullOrEmpty(order))
                sb.Append(string.Format(" order by {0}", order));
            return sb.ToString();                
        }

        private string BuildMSSql(string list, string source, string where, string group, string order, string range)
        {
            StringBuilder sb = new StringBuilder();
            string finalFormat = "select * from ( {0} ) a where {1}";
            string rangeWhere = null;
            
            //分析range语句
            if (!string.IsNullOrEmpty(range))
            {
                if (!range_reg.IsMatch(range))
                    throw new WMSSysException("range语句不符合格式！");
                Match rangeMatch = range_reg.Match(range);

                string startIndexStr = rangeMatch.Groups["startIndex"].Value;
                string endStartIndexStr = rangeMatch.Groups["endIndex"].Value;
                string dataKey = rangeMatch.Groups["datakey"].Value;

                //如果存在键值，则加入排序条件
                if (!string.IsNullOrEmpty(dataKey))
                {
                    //当且仅当datakey不在排序条件中时，才加入
                    if (!string.IsNullOrEmpty(order) && order.Trim().ToUpper().IndexOf(dataKey.Trim().ToUpper()) == -1)
                    {
                        order += "," + dataKey;
                    }
                    else if (string.IsNullOrEmpty(order))
                        order = dataKey;
                }
                else
                {
                    if (string.IsNullOrEmpty(order))
                        throw new WMSSysException("含有range命令时，必须有datakey或者order子句");
                }
                //将排序条件加入到获得行号的条件中，避免二次排序
                list += string.Format(",row_number() over  (order  by  {0} ) as  ROWNUM", order);
                //还是得有order，否则界面排序不对
                //order = null;//清空排序条件，最终sql不再含有order by

                rangeWhere = string.Format(" (ROWNUM>={0} and ROWNUM<={1})", startIndexStr, endStartIndexStr);
            }
            //else if (!string.IsNullOrEmpty(order))
            //{
            //    list += string.Format(",row_number() over  (order  by  {0} ) as  ROWNUM", order);
            //}
            sb.Append(string.Format("select {0} from {1}", list, source));
            if (!string.IsNullOrEmpty(where))
                sb.Append(string.Format(" where {0}", where));
            if (!string.IsNullOrEmpty(group))
                sb.Append(string.Format(" group by {0}", group));
            //当不含range时，才能使用order，否则查询出错
            if (string.IsNullOrEmpty(rangeWhere)&&!string.IsNullOrEmpty(order))
                sb.Append(string.Format(" order by {0}", order));

            string result=sb.ToString();
            if (!string.IsNullOrEmpty(rangeWhere))
            {
                result=string.Format(finalFormat, result, rangeWhere);
                if(!string.IsNullOrEmpty(order))
                    result+= string.Format(" order by {0}", order);
            }
            return result;    
        }
    }
}
